<?php

class Application_Model_DbTable_Transaction extends Zend_Db_Table_Abstract
{

    protected $_name = 'Transaction';

    public function addTransaction($code, $date, $shop_id, $buyer_id, $details,
            $total_payment, $receiver, $receiver_address, $last_update ){
        $data = array(
            "code" => $code,
            "date" => $date,
            "shop_id" => $shop_id,
            "buyer_id" => $buyer_id,
            "details" => $details,
            "total_payment" => $total_payment,
            "receiver" => $receiver,
            "receiver_address" => $receiver_address,
            "last_update" => $last_update
        );
        $this->insert($data);
    }
    
    public function addReceiver($transactionId,$name,$ic,$relation){
        $db = $this->getAdapter();
        $db->query("INSERT INTO Receiver(transaction_id,name,ic_number,relationship) ".
				"VALUES ($transactionId,'$name','$ic','$relation')");
    }
    
    public function getId($code){
        $row = $this->fetchRow("code = '$code'");
        return ($row)?$row["transaction_id"]:null;
    }
	
	public function getPUK($id){
		$row = $this->fetchRow("transaction_id = $id");
        return ($row)?$row["puk"]:null;
	}
	
	public function getStatus($id){
		$row = $this->fetchRow("transaction_id = $id");
        return ($row)?$row["transaction_status_id"]:null;
	}

    public function deleteTransaction($id){
        $this->delete("transaction_id = ".$id);
    }

    public function getTransaction($id){
        $query = $this->select();
        $query->from(array('t'=>'Transaction'));
        $query->joinInner(array('ts'=>'Transaction_status'),'t.transaction_status_id = ts.status_id',array('description'=>'ts.description'));
        $query->joinInner(array('b'=>'Buyer'),'b.buyer_id = t.buyer_id',array('b_name'=>'b.name','b_address'=>'b.address','b_email'=>'b.email','b_home_phone'=>'b.home_phone','b_mobile_phone'=>'b.mobile_phone'));
        $query->joinInner(array('s'=>'Shop'),'s.shop_id = t.shop_id',array('s_name'=>'s.name','s_address'=>'s.address','s_city'=>'s.city','s_phone'=>'s.phone','s_email'=>'s.email'));
        $query->setIntegrityCheck(false);
        $query->where("transaction_id = ".$id);
        return $this->fetchRow($query);
    }
	
	public function getReceiver($transactionId){
		$receiverTable = new Zend_Db_Table('Receiver');
		return $receiverTable->fetchRow("transaction_id = $transactionId");
		//$query = $this->select();
		//$query->from(array('r'=>'Receiver'));
		//return $this->fetchRow($query);
	}
    /*
    public function getAll($where){
        $query = $this->select();
        $query->from(array('t'=>'Transaction'));
        $query->joinInner(array('ts'=>'Transaction_status'),'t.transaction_status_id = ts.status_id',array('description'=>'ts.description'));
        $query->joinInner(array('b'=>'Buyer'),'b.buyer_id = t.buyer_id',array('b_name'=>'b.name'));
        $query->joinInner(array('s'=>'Shop'),'s.shop_id = t.shop_id',array('s_name'=>'s.name'));
        $query->setIntegrityCheck(false);
        if($where) $query->where($where);
        return $this->fetchAll($query);
    }
    */
    public function getAll($number,$page,$where = null,$order = null,$method = null){
        // start and end row to display
        $start = ($page - 1) * $number + 1;
        $end = $start + $number - 1 ;

        // WHERE clause
        $where = ($where)? " WHERE ".$where : "";

        // ORDER BY clause
        $order = ($order)? $order : "transaction_id";
        $method = ($method)? $method : "ASC";


        $db = $this->getAdapter();
        $sql = "SELECT t.* , ts.description AS description, b.name AS b_name, s.name AS s_name ".
            "FROM (SELECT rownumber = ROW_NUMBER() OVER (ORDER BY tr.$order $method), tr.* FROM [Transaction] AS tr".$where.") as t ".
            "INNER JOIN Transaction_status as ts ON t.transaction_status_id = ts.status_id ".
            "INNER JOIN Buyer as b ON b.buyer_id = t.buyer_id ".
            "INNER JOIN Shop as s ON s.shop_id = t.shop_id ".
            "WHERE rownumber >= $start AND rownumber <= $end";
      
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }

    public function countStatus(){
        $query = $this->select();
        $query->from(array("t"=>"Transaction"),array("total"=>"count(transaction_id)"));
        $query->joinRight(array("ts"=>"Transaction_status"), "t.transaction_status_id = ts.status_id", array("status_id","description"));
        $query->group(array("description","ts.status_id"));
        $query->order("ts.status_id");
        $query->setIntegrityCheck(false);
        return $this->fetchAll($query);
    }

    public function updateStatus($transactionId,$statusId){
        $data = array("transaction_status_id"=>$statusId);
        $this->update($data, "transaction_id = ".$transactionId);
    }

    public function insertReason($transactionId,$reason){
        $data = array("cancel_reason"=>$reason);
        $this->update($data, "transaction_id = ".$transactionId);
    }

    public function lastUpdate($transactionId,$time){
        $data = array("last_update"=>$time);
        $this->update($data, "transaction_id = ".$transactionId);
    }
    
    public function setPaymentTime($transactionId,$time){
        $data = array("payment_date"=>$time);
        $this->update($data, "transaction_id = ".$transactionId);
    }
    
    public function setDeliveryTime($transactionId,$time){
        $data = array("delivery_date"=>$time);
        $this->update($data, "transaction_id = ".$transactionId);
    }

    public function countRows($where = null){
        
        $query = $this->select();
        $query->from(array("t"=>"Transaction"),array("total"=>"count(transaction_id)"));
        if($where) $query->where ($where);
        $row = $this->fetchRow($query);
        return $row["total"];
        
    }
}

